package cn.com.connext.scrm.repository.master;

import cn.com.connext.scrm.domain.enumeration.AuditStatus;
import cn.com.connext.scrm.domain.enumeration.DeleteStatus;
import cn.com.connext.scrm.domain.master.Note;
import cn.com.connext.scrm.service.dto.NoteDTO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.ZonedDateTime;
import java.util.List;

/**
 * Created by YANG205 on 2018/6/27.
 */
@Repository
public interface NoteRepository extends JpaRepository<Note, String>, JpaSpecificationExecutor {

    //所有帖子(未删除，按照noteType区分)
    @Query(value = "select n from Note n where n.userId = :userId and n.noteType = :noteType and n.deleteStatus = 'ACTIVATED' order by n.createTime desc")
    List<Note> findAllWithoutDelete(@Param("userId") String userId, @Param("noteType") Integer noteType);

    //所有帖子(未删除)
    @Query(value = "select a from Note a where a.deleteStatus = 'ACTIVATED' and a.userId = :userId order by a.createTime DESC")
    List<Note> findAllNotes(@Param("userId") String userId);

    //所有帖子(未删除，已审核)
    @Query(value = "select a from Note a where a.deleteStatus = 'ACTIVATED' and a.auditStatus = 'AUDITED' order by a.createTime DESC")
    List<Note> findAll();

    //所有未被删除的帖子Id
    @Query(value = "select n.id from Note n where n.deleteStatus = 'ACTIVATED'")
    List<String> findAllNoteIdWithoutDelete();

    @Query(value = "select n from Note n where n.deleteStatus = 'ACTIVATED' and n.relevantProducts like CONCAT('%',:wid,'%')")
    List<Note> getNotesByWid(@Param("wid") String wid);

    @Query(value = "select n from Note n where n.deleteStatus = 'ACTIVATED' and n.auditStatus = 'AUDITED' order by n.isTop desc, n.updateTime desc ")
    List<Note> findAllNote();

    List<Note> findByUserIdInOrderByIsTopDescUpdateTimeDesc(List<String> stringList);

    List<Note> findByIsRateOrderByIsTopDescUpdateTimeDesc(boolean b);

    @Query(value = "select n from Note n where n.deleteStatus = 'ACTIVATED' and n.relevantProducts like CONCAT('%',:goodId,'%')")
    List<Note> getCommentById(@Param("goodId") String goodId);

    @Query(value = "select n from Note n where n.deleteStatus = 'ACTIVATED' and n.id in (?1) ")
    List<Note> findByIds(List<String> list);

    @Modifying
    @Query(value = "update note set is_top = true , update_time = now() where id = ?1 ", nativeQuery = true)
    void top(String id);

    @Modifying
    @Query(value = "update note set is_top = false , update_time = now() where id = ?1 ", nativeQuery = true)
    void unTop(String id);

    List<Note> findByActivityIdAndAuditStatusAndDeleteStatusOrderByCreateTimeDesc(String activityId, AuditStatus audited, DeleteStatus activated);

    @Query(value = "select a.* from note a where a.is_answer = 1 and a.delete_status = 'ACTIVATED' and a.audit_status = 'AUDITED'", nativeQuery = true)
    List<Note> findAnswer();

    @Query(value = "select a.* from note a where a.is_answer = 1 and a.delete_status = 'ACTIVATED' and a.is_hot = 0 and audit_result = 1", nativeQuery = true)
    List<Note> findOtherAnswer();

    @Query(value = "select a.* from note a where a.is_answer = 1 and a.delete_status = 'ACTIVATED' and a.is_hot = 1 and audit_result = 1", nativeQuery = true)
    List<Note> findHotAnswer();

    @Query(value = "select count(1) from note a where a.is_answer = 1 and a.delete_status = 'ACTIVATED' and audit_result =1 and answer_type = :answerType group  by answer_type", nativeQuery = true)
    Integer countByAnswerType(@Param("answerType") String answerType);

    @Query(value = "select a.* from note a where a.delete_status = 'ACTIVATED' and a.audit_status = 'AUDITED' and  a.create_time between :startTime and :endTime", nativeQuery = true)
    List<Note> findAnswerByTime(@Param("startTime") ZonedDateTime startTime, @Param("endTime") ZonedDateTime endTime);

    //查询当月审核通过的
    @Query(value = "select a.* from note a where  a.audit_status = 'AUDITED' and user_id= :userId AND date_format(create_time,'%Y-%m')=date_format(now(),'%Y-%m') ", nativeQuery = true)
    List<Note> findNoteByUserIdAndMonth(@Param("userId") String userId);

    //查询当天审核通过的
    @Query(value = "select a.* from note a where  a.audit_status = 'AUDITED' and user_id= :userId AND to_days(create_time) =TO_DAYS(NOW()) ", nativeQuery = true)
    List<Note> findNoteByUserIdAndDay(@Param("userId") String userId);
}
